﻿#region Copyright (C) Sector7 Software Studio. All rights reserved
/*******************************************************************************************
 * Creation
 *      Author:         Wolf Lai
 *      Date:           2013/6/15 星期六 21:15:21
 *      Version:        
 * Modification 
 *      Author: 
 *      Date: 
 *      Description: 
 *      Version:
 *******************************************************************************************/
#endregion
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Sector7.Framework.EntityType;
using Sector7.Framework.DB;

namespace Sector7.Framework.DAO.Standard
{
    /// <summary>
    /// 个人信息数据访问对象
    /// </summary>
    public class CitizenDAO : DAOBase<S7_Citizen>, ICitizenDAO
    {
        public int Count(string columnName, object value)
        {
            string sql = string.Format(@"
                SELECT
                    COUNT(*)
                FROM S7_Citizen
                WHERE {0}=@{0}", columnName);

            using (DBHelper helper = new DBHelper())
            {
                int count = -1;

                SqlDataReader reader = helper.ExecuteQuery(sql,
                    DBUtil.CreateSqlParameter(value, columnName));

                if (reader.Read())
                {
                    count = Convert.ToInt32(reader.GetValue(0));
                }

                return count;
            }
        }

        /// <summary>
        /// 根据证件类型和证件号获取人员信息
        /// </summary>
        /// <param name="identityType">证件号类型ID</param>
        /// <param name="identityId">证件号</param>
        /// <returns></returns>
        public S7_Citizen GetCitizenByIdentityId(int identityTypeId, string identityId)
        {
            string sql = @"
                SELECT * FROM S7_Citizen
                    WHERE 1 = 1
                    and IdentityTypeId = @IdentityTypeId
                    and IdentityId = @IdentityId";

            using (DBHelper helper = new DBHelper())
            {
                SqlDataReader reader = helper.ExecuteQuery(sql,
                    DBUtil.CreateSqlParameter(identityTypeId, "IdentityTypeId"),
                    DBUtil.CreateSqlParameter(identityId, "IdentityId"));

                S7_Citizen citizen = null;
                while (reader.Read())
                {
                    citizen = new S7_Citizen();
                    DBUtil.ReadData(reader, citizen);
                }

                return citizen;
            }
        }

        /// <summary>
        /// 添加个人信息数据
        /// </summary>
        /// <param name="citizen">个人信息数据</param>
        /// <returns></returns>
        public override object Create(S7_Citizen citizen)
        {
            string sql = @"
                INSERT INTO S7_Citizen
                        (CitizenId
                        ,FullName
                        ,GenderId
                        ,Birthday
                        ,IdentityTypeId
                        ,IdentityId
                        ,WorkingUnitName
                        ,Telephone
                        ,Mobilephone
                        ,ContacterName
                        ,ContacterPhone
                        ,ContacterRelationId
                        ,CurrentAddrIsPermanent
                        ,EthnicId
                        ,EducationId
                        ,VocationId
                        ,MaritalStatusId
                        ,NationalityId
                        ,Birthplace
                        ,PermanetAddr
                        ,PermanetPostcode
                        ,CurrentAddr
                        ,CurrentPostcode
                        ,SpellCode
                        ,Username)
                    VALUES
                        (@CitizenId
                        ,@FullName
                        ,@GenderId
                        ,@Birthday
                        ,@IdentityTypeId
                        ,@IdentityId
                        ,@WorkingUnitName
                        ,@Telephone
                        ,@Mobilephone
                        ,@ContacterName
                        ,@ContacterPhone
                        ,@ContacterRelationId
                        ,@CurrentAddrIsPermanent
                        ,@EthnicId
                        ,@EducationId
                        ,@VocationId
                        ,@MaritalStatusId
                        ,@NationalityId
                        ,@Birthplace
                        ,@PermanetAddr
                        ,@PermanetPostcode
                        ,@CurrentAddr
                        ,@CurrentPostcode
                        ,@SpellCode
                        ,@Username)";

            using (DBHelper helper = new DBHelper())
            {
                return helper.ExecuteNonQuery(sql,
                    DBUtil.CreateSqlParameter(citizen.CitizenId, "CitizenId"),
                    DBUtil.CreateSqlParameter(citizen.FullName, "FullName"),
                    DBUtil.CreateSqlParameter(citizen.GenderId, "GenderId"),
                    DBUtil.CreateSqlParameter(citizen.Birthday, "Birthday"),
                    DBUtil.CreateSqlParameter(citizen.IdentityTypeId, "IdentityTypeId"),
                    DBUtil.CreateSqlParameter(citizen.IdentityId, "IdentityId"),
                    DBUtil.CreateSqlParameter(citizen.WorkingUnitName, "WorkingUnitName"),
                    DBUtil.CreateSqlParameter(citizen.Telephone, "Telephone"),
                    DBUtil.CreateSqlParameter(citizen.Mobilephone, "Mobilephone"),
                    DBUtil.CreateSqlParameter(citizen.ContacterName, "ContacterName"),
                    DBUtil.CreateSqlParameter(citizen.ContacterPhone, "ContacterPhone"),
                    DBUtil.CreateSqlParameter(citizen.ContacterRelationId, "ContacterRelationId"),
                    DBUtil.CreateSqlParameter(citizen.CurrentAddrIsPermanent, "CurrentAddrIsPermanent"),
                    DBUtil.CreateSqlParameter(citizen.EthnicId, "EthnicId"),
                    DBUtil.CreateSqlParameter(citizen.EducationId, "EducationId"),
                    DBUtil.CreateSqlParameter(citizen.VocationId, "VocationId"),
                    DBUtil.CreateSqlParameter(citizen.MaritalStatusId, "MaritalStatusId"),
                    DBUtil.CreateSqlParameter(citizen.NationalityId, "NationalityId"),
                    DBUtil.CreateSqlParameter(citizen.Birthplace, "Birthplace"),
                    DBUtil.CreateSqlParameter(citizen.PermanetAddr, "PermanetAddr"),
                    DBUtil.CreateSqlParameter(citizen.PermanetPostcode, "PermanetPostcode"),
                    DBUtil.CreateSqlParameter(citizen.CurrentAddr, "CurrentAddr"),
                    DBUtil.CreateSqlParameter(citizen.CurrentPostcode, "CurrentPostcode"),
                    DBUtil.CreateSqlParameter(citizen.SpellCode, "SpellCode"),
                    DBUtil.CreateSqlParameter(citizen.Username, "Username"));
            }
        }

        /// <summary>
        /// 更新个人信息
        /// </summary>
        /// <param name="citizen">个人信息</param>
        /// <returns></returns>
        public int Update(S7_Citizen citizen)
        {
            string sql = @"
                UPDATE S7_Citizen Set
                        FullName = @FullName
                        ,GenderId = @GenderId
                        ,Birthday = @Birthday
                        ,IdentityTypeId = @IdentityTypeId
                        ,IdentityId = @IdentityId
                        ,WorkingUnitName = @WorkingUnitName
                        ,Telephone = @Telephone
                        ,Mobilephone = @Mobilephone
                        ,ContacterName = @ContacterName
                        ,ContacterPhone = @ContacterPhone
                        ,ContacterRelationId = @ContacterRelationId
                        ,CurrentAddrIsPermanent = @CurrentAddrIsPermanent
                        ,EthnicId = @EthnicId
                        ,EducationId = @EducationId
                        ,VocationId = @VocationId
                        ,MaritalStatusId = @MaritalStatusId
                        ,NationalityId = @NationalityId
                        ,Birthplace = @Birthplace
                        ,PermanetAddr = @PermanetAddr
                        ,PermanetPostcode = @PermanetPostcode
                        ,CurrentAddr = @CurrentAddr
                        ,CurrentPostcode = @CurrentPostcode
                        ,SpellCode = @SpellCode
                        ,Username = @Username
                    where CitizenId = @CitizenId";

            using (DBHelper helper = new DBHelper())
            {
                return helper.ExecuteNonQuery(sql,
                    DBUtil.CreateSqlParameter(citizen.FullName, "FullName"),
                    DBUtil.CreateSqlParameter(citizen.GenderId, "GenderId"),
                    DBUtil.CreateSqlParameter(citizen.Birthday, "Birthday"),
                    DBUtil.CreateSqlParameter(citizen.IdentityTypeId, "IdentityTypeId"),
                    DBUtil.CreateSqlParameter(citizen.IdentityId, "IdentityId"),
                    DBUtil.CreateSqlParameter(citizen.WorkingUnitName, "WorkingUnitName"),
                    DBUtil.CreateSqlParameter(citizen.Telephone, "Telephone"),
                    DBUtil.CreateSqlParameter(citizen.Mobilephone, "Mobilephone"),
                    DBUtil.CreateSqlParameter(citizen.ContacterName, "ContacterName"),
                    DBUtil.CreateSqlParameter(citizen.ContacterPhone, "ContacterPhone"),
                    DBUtil.CreateSqlParameter(citizen.ContacterRelationId, "ContacterRelationId"),
                    DBUtil.CreateSqlParameter(citizen.CurrentAddrIsPermanent, "CurrentAddrIsPermanent"),
                    DBUtil.CreateSqlParameter(citizen.EthnicId, "EthnicId"),
                    DBUtil.CreateSqlParameter(citizen.EducationId, "EducationId"),
                    DBUtil.CreateSqlParameter(citizen.VocationId, "VocationId"),
                    DBUtil.CreateSqlParameter(citizen.MaritalStatusId, "MaritalStatusId"),
                    DBUtil.CreateSqlParameter(citizen.NationalityId, "NationalityId"),
                    DBUtil.CreateSqlParameter(citizen.Birthplace, "Birthplace"),
                    DBUtil.CreateSqlParameter(citizen.PermanetAddr, "PermanetAddr"),
                    DBUtil.CreateSqlParameter(citizen.PermanetPostcode, "PermanetPostcode"),
                    DBUtil.CreateSqlParameter(citizen.CurrentAddr, "CurrentAddr"),
                    DBUtil.CreateSqlParameter(citizen.CurrentPostcode, "CurrentPostcode"),
                    DBUtil.CreateSqlParameter(citizen.SpellCode, "SpellCode"),
                    DBUtil.CreateSqlParameter(citizen.Username, "Username"),
                    DBUtil.CreateSqlParameter(citizen.CitizenId, "CitizenId"));
            }
        }

        #region Structure
        public CitizenDAO()
        {
            m_tableName = "S7_Citizen";
            m_primaryKeyName = "CitizenId";
        }
        #endregion
    }
}
